﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using bdnt_pets_system_Entity;
using System.Data;

namespace bdnt_pets_system_Bo.DataManager
{
    public class PartManager
    {
        //AccessHelperSQL accessHelper = AccessHelperSQL.GetInstance();
        AccessHelper accessHelper = AccessHelper.GetInstance();
        public List<Tbl_Part> GetObjListByCondition(Tbl_Part obj, int pageSize, int currentPage)
        {
            //            StringBuilder sql = new StringBuilder(@"select row_number() over(order by en_PartName,PartName) recordId,*
            //            ,replace(replace(replace(replace(replace(replace((
            //            select SYS_XMLAGG(SYS_XMLGEN(pr.StationId)) from Tbl_PartSta_Relation pr              
            //            where pr.PartNo=pt.PartNo               
            //            ) ,'</STATIONID>',','),'<STATIONID>',''),'<?xml version=""1.0""?>',''),'<ROWSET>',''),'</ROWSET>',''),chr(10),'')
            //             StationIds 
            //            from Tbl_Part pt where 1=1");

            StringBuilder sql = new StringBuilder(@"select row_number() over(order by en_PartName,PartName) recordId,pt.*
            ,RTRIM(replace(replace(replace(replace(replace(replace((
            select SYS_XMLAGG(SYS_XMLGEN(pr.StationId)) from Tbl_PartSta_Relation pr              
            where pr.PartNo=pt.PartNo               
            ) ,'</STATIONID>',','),'<STATIONID>',''),'<?xml version=""1.0""?>',''),'<ROWSET>',''),'</ROWSET>',''),chr(10),''),',')
             StationIds 
            from Tbl_Part pt where 1=1");
            if (obj != null)
            {
                if (!string.IsNullOrEmpty(obj.PartNo))
                    sql = sql.AppendFormat(@" and PartNo like '%{0}%'", obj.PartNo);
                if (!string.IsNullOrEmpty(obj.PartName))
                {
                    sql = sql.AppendFormat(@" and (Upper(PartName) like '%{0}%' or Upper(en_PartName) like '%{1}%')", obj.PartName.ToUpper(), obj.PartName.ToUpper());
                }
                //if (!string.IsNullOrEmpty(obj.PartName))
                //    sql = sql.AppendFormat(@" and PartName like '%{0}%'", obj.PartName);
                //if (!string.IsNullOrEmpty(obj.en_PartName))
                //    sql = sql.AppendFormat(@" and en_PartName like '%{0}%'", obj.en_PartName);
                if (!string.IsNullOrEmpty(obj.PartColor))
                    sql = sql.AppendFormat(@" and PartColor like '%{0}%'", obj.PartColor);
                if (obj.IsImportantSelect != -1)
                    sql = sql.AppendFormat(@" and IsImportant = {0}", obj.IsImportantSelect);
            }
            int startNum = pageSize * (currentPage > 0 ? currentPage - 1 : 0);
            int endNum = pageSize * currentPage;
            sql.Insert(0, "select * from (");
            sql.AppendFormat(@") re where re.recordId>{0} and re.recordId<={1}", startNum, endNum);
            var dt = accessHelper.ExcuteDataTable(sql.ToString());
            List<Tbl_Part> returnlist = new List<Tbl_Part>();
            foreach (DataRow dr in dt.Rows)
            {
                returnlist.Add(new Tbl_Part
                {
                    Id = Convert.ToInt32(dr["Id"]),
                    PartNo = dr["PartNo"] == DBNull.Value ? "" : dr["PartNo"].ToString(),
                    PartName = dr["PartName"] == DBNull.Value ? "" : dr["PartName"].ToString(),
                    en_PartName = dr["en_PartName"] == DBNull.Value ? "" : dr["en_PartName"].ToString(),
                    PartColor = dr["PartColor"] == DBNull.Value ? "" : dr["PartColor"].ToString(),
                    Remark = dr["Remark"] == DBNull.Value ? "" : dr["Remark"].ToString(),
                    IsImportant = dr["IsImportant"] == DBNull.Value ? false : dr["IsImportant"].ToString() == "1" ? true : false,
                    StationIds = dr["StationIds"] == DBNull.Value ? "" : dr["StationIds"].ToString(),
                    FuncitionGroup = dr["FUNCTIONGROUP"] == DBNull.Value ? "" : dr["FUNCTIONGROUP"].ToString(),
                    MaterialCode = dr["MATERIALCODE"] == DBNull.Value ? "" : dr["MATERIALCODE"].ToString(),
                    Supplier = dr["Supplier"] == DBNull.Value ? "" : dr["Supplier"].ToString(),
                    Weight = dr["Weight"] == DBNull.Value ? 0 : float.Parse(dr["Weight"].ToString())
                   
                });
            }
            return returnlist;
        }

        public int GetObjListByConditionCount(Tbl_Part obj)
        {
            StringBuilder sql = new StringBuilder("select count(0) allCount from Tbl_Part where 1=1");
            if (obj != null)
            {
                if (!string.IsNullOrEmpty(obj.PartNo))
                    sql = sql.AppendFormat(@" and PartNo like '%{0}%'", obj.PartNo);
                if (!string.IsNullOrEmpty(obj.PartName))
                {
                    sql = sql.AppendFormat(@" and (Upper(PartName) like '%{0}%' or Upper(en_PartName) like '%{1}%')", obj.PartName.ToUpper(), obj.PartName.ToUpper());
                }

                if (!string.IsNullOrEmpty(obj.PartColor))
                    sql = sql.AppendFormat(@" and PartColor like '%{0}%'", obj.PartColor);
                if (obj.IsImportantSelect != -1)
                    sql = sql.AppendFormat(@" and IsImportant = {0}", obj.IsImportantSelect);
            }
            var dt = accessHelper.ExecuteScalar(sql.ToString());
            if (dt != null)
                return Int32.Parse(dt.ToString());
            return 0;
        }

        public bool SaveObj(Tbl_Part cuObj)
        {
            StringBuilder sql = new StringBuilder();
            if (cuObj.Id == 0)
            {
                sql.AppendFormat(@"INSERT INTO Tbl_Part
               (PartNo,PartName,en_PartName,PartColor,Remark,IsImportant,MATERIALCODE,FUNCTIONGROUP,WEIGHT,SUPPLIER)
                VALUES('{0}','{1}','{2}','{3}','{4}',{5},'{6}','{7}',{8},'{9}')",
                cuObj.PartNo, cuObj.PartName, cuObj.en_PartName, cuObj.PartColor,
               cuObj.Remark, cuObj.IsImportant ? 1 : 0, cuObj.MaterialCode, cuObj.FuncitionGroup,
               cuObj.Weight, cuObj.Supplier);
                string sql1 = "select tbl_part_seq.currval from dual";
                var cure = accessHelper.ExecuteScalar(sql.ToString(), sql1);
                if (cure == null)
                    return false;

                if (string.IsNullOrEmpty(cuObj.StationIds))
                    return true;

                sql = new StringBuilder();
                sql.AppendLine(string.Format(@"INSERT INTO Tbl_PartSta_Relation(StationId,PartNo)
                        select Id,'{0}' from Tbl_Station where id in ({1})", cuObj.PartNo, cuObj.StationIds));
                return accessHelper.ExcultData(sql.ToString());
            }
            else
            {
                sql.AppendFormat(@"UPDATE Tbl_Part
                   SET PartNo ='{0}'
                  ,PartName ='{1}'
                 ,en_PartName = '{2}'
                 ,PartColor = '{3}'
                ,Remark = '{4}'
                ,IsImportant = '{5}'
                ,MATERIALCODE='{6}'
                ,FUNCTIONGROUP='{7}'
                ,WEIGHT={8}
                ,SUPPLIER='{9}'
                WHERE Id={10}", cuObj.PartNo, cuObj.PartName, cuObj.en_PartName, cuObj.PartColor,
                              cuObj.Remark, cuObj.IsImportant ? 1 : 0, cuObj.MaterialCode,
                              cuObj.FuncitionGroup, cuObj.Weight, cuObj.Supplier, cuObj.Id);
                var cure = accessHelper.ExcultData(sql.ToString());
                if (!cure)
                    return false;

                sql = new StringBuilder();
                sql = sql.AppendFormat(@"call UpdatePart('{0}','{1}')", cuObj.PartNo, cuObj.StationIds);
                return accessHelper.ExcultProc(sql.ToString());
            }
        }

        public Tbl_Part GetObjById(int objId)
        {
            Tbl_Part cur = new Tbl_Part();
            var sql = string.Format(@"select * from Tbl_Part where Id={0}", objId);
            var dt = accessHelper.ExcuteDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                cur = new Tbl_Part
                {
                    Id = Convert.ToInt32(dr["Id"]),
                    PartNo = dr["PartNo"] == DBNull.Value ? "" : dr["PartNo"].ToString(),
                    PartName = dr["PartName"] == DBNull.Value ? "" : dr["PartName"].ToString(),
                    en_PartName = dr["en_PartName"] == DBNull.Value ? "" : dr["en_PartName"].ToString(),
                    PartColor = dr["PartColor"] == DBNull.Value ? "" : dr["PartColor"].ToString(),
                    Remark = dr["Remark"] == DBNull.Value ? "" : dr["Remark"].ToString(),
                    IsImportant = dr["IsImportant"] == DBNull.Value ? false : dr["IsImportant"].ToString() == "1" ? true : false,
                    FuncitionGroup = dr["FuncitionGroup"] == DBNull.Value ? "" : dr["FuncitionGroup"].ToString(),
                    MaterialCode = dr["MaterialCode"] == DBNull.Value ? "" : dr["MaterialCode"].ToString(),
                    Supplier = dr["Supplier"] == DBNull.Value ? "" : dr["Supplier"].ToString(),
                    Weight = dr["Weight"] == DBNull.Value ? 0 : float.Parse(dr["Weight"].ToString())

                };
            }
            return cur;
        }

        public bool DeleteCuObj(Tbl_Part cuObj)
        {
            string sql = string.Format(@"call DeletePart('{0}')", cuObj.PartNo);
            return accessHelper.ExcultData(sql);
        }

    }
}
